package com.xxxcompany.wordbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.sun.xml.internal.bind.v2.runtime.Name;
import com.xxxcompany.utils.DbUtils;
import com.xxxcompany.wordbook.entity.User;
import com.xxxcompany.wordbook.entity.Word;

public class WordDAOImpl implements IWordDAO {

	@Override
	public int insert(Word w) throws SQLException {

		//
		Connection conn = DbUtils.getConnection();
		//
		String sql = "insert into word value(null,?,?,?);";
		PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, w.getWord());
		stmt.setString(2, w.getChinese());
		stmt.setInt(3, w.getUserId());

		int result = stmt.executeUpdate();

		DbUtils.destoryResource(conn, stmt, null);

		return result;
	}

	@Override
	public List<Word> findAll() throws SQLException {

		//
		Connection conn = DbUtils.getConnection();
		//
		String sql = "select * from word;";
		PreparedStatement stmt = conn.prepareStatement(sql);

		ResultSet rs = stmt.executeQuery();

		List<Word> result = new ArrayList<>();
		while (rs.next()) {
			int id = rs.getInt("id");
			String word = rs.getString("word");
			String chinese = rs.getString("chinese");
			int userId = rs.getInt("user_id");
			Word w = new Word(id, word, chinese, userId);

			result.add(w);
		}

		DbUtils.destoryResource(conn, stmt, null);

		return result;
	}

	@Override
	public List<Word> findAll(int userId) throws SQLException {
		//
		Connection conn = DbUtils.getConnection();
		//
		String sql = "select * from word where user_id=?;";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, userId);

		ResultSet rs = stmt.executeQuery();

		List<Word> result = new ArrayList<>();
		while (rs.next()) {
			int id = rs.getInt("id");
			String word = rs.getString("word");
			String chinese = rs.getString("chinese");
			Word w = new Word(id, word, chinese, userId);

			result.add(w);
		}

		DbUtils.destoryResource(conn, stmt, null);

		return result;
	}

	@Override
	public Word findById(int id) throws SQLException {
		// 1. 获取连接
		Connection conn = DbUtils.getConnection();
		// 2. 查询对象
		Statement stmt = conn.createStatement();
		// 3. 执行查询
		String sql = "select * from word where id=" + id;
		ResultSet rs = stmt.executeQuery(sql);
		Word result = null;
		if (rs.next()) {
			int wid = rs.getInt(1);
			String wname = rs.getString(2);
			String chinese = rs.getString(3);
			int uid = rs.getInt(4);
			result = new Word(wid, wname, chinese, uid);

		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);
		return result;

	}

	@Override
	public int deleteById(int id) throws SQLException {
		// TODO Auto-generated method stub
		// 1. 获取连接
		Connection conn = DbUtils.getConnection();
		// 2. 查询对象
		Statement stmt = conn.createStatement();
		// 3. 执行查询
		String sql = "delete from word where id=" + id;
		int result = stmt.executeUpdate(sql);
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, null);
		return result;
	}

	@Override
	public int updateById(Word w) throws SQLException {
		// TODO Auto-generated method stub
		//
		Connection conn = DbUtils.getConnection();
		//
		String sql = "update word set word=?,chinese=? where id=?;";
		PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, w.getWord());
		stmt.setString(2, w.getChinese());
		stmt.setInt(3, w.getId());

		int result = stmt.executeUpdate();

		DbUtils.destoryResource(conn, stmt, null);

		return result;
	}

}